package top.sehnsucht.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import top.sehnsucht.dao.ExcelDao;
import top.sehnsucht.dao.impl.ExcelDaoImpl;
import top.sehnsucht.service.ExcelService;
import top.sehnsucht.service.impl.ExcelServiceImpl;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;

/**
 * @Description: excel工具类
 * @Author: Cai
 * @CreateTime: 2021/11/19
 */

public class ExcelUtil {
    /**
     * 将name文件从数据库中读取出来
     * @throws IOException
     */
    public static void createExcelFile(String name, String path) throws Exception {
        ExcelDao dao = new ExcelDaoImpl();
        ArrayList<ArrayList<String>> allList = dao.dataToExcel(name);

        File excelFile = new File(path);
        if (!excelFile.exists()) {
            excelFile.mkdirs();
        }

        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet();
        CellStyle cellStyle = wb.createCellStyle();
        // 字体
        Font font = wb.createFont();
        font.setFontName("等线");
        font.setFontHeightInPoints((short) 20);
        cellStyle.setFont(font);
        for (int i = 0; i < allList.size(); i++) {
            // 3、创建行对象
            Row row = sheet.createRow(i);
            for (int j = 0; j < allList.get(i).size(); j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(allList.get(i).get(j));
                cell.setCellStyle(cellStyle);
            }
        }

        OutputStream os = new FileOutputStream(path + "\\" + name + ".xls");
        wb.write(os);
        os.close();
    }

    /**
     * 将filePath的文件存入数据库
     * @param name
     * @param filePath
     * @throws IOException
     */
    public static void readExcelFile(String name, String filePath) throws IOException {
        ExcelDao dao = new ExcelDaoImpl();
        ExcelService service = new ExcelServiceImpl(dao);

        File xlsFile = new File(filePath);

        // 工作表
        Workbook workbook = WorkbookFactory.create(xlsFile);
        // 表个数。
        int numberOfSheets = workbook.getNumberOfSheets();
        // 遍历表。
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);

            // 行数。
            int rowNumbers = sheet.getLastRowNum() + 1;

            // Excel第一行。
            Row temp = sheet.getRow(0);
            if (temp == null) {
                continue;
            }

            int cells = temp.getPhysicalNumberOfCells();

            ArrayList<String> list = new ArrayList<String>();
            // 读数据。
            for (int row = 0; row < rowNumbers; row++) {
                if (row == 0) {
                    Row r = sheet.getRow(row);
                    for (int col = 0; col < cells; col++) {
                        if (r.getCell(col) == null) {
                            list.add("null");
                        } else {
                            list.add(r.getCell(col).toString());
                        }
                    }
                    System.out.println(list);
                    service.createExcel(name,list);
                    continue;
                }
                ArrayList<String> data = new ArrayList<String>();
                Row r = sheet.getRow(row);
                for (int col = 0; col < cells; col++) {
                    if (r.getCell(col) == null) {
                        data.add("null");
                    } else {
                        data.add(r.getCell(col).toString());
                    }
                }
                service.addExcel(name,list,data);
            }
        }
    }
}
